package auction.chapter08;
/*
DECLARE var_name[,...] type [DEFAULT value]: declare local variables
SET var_name = expr [, var_name = expr] : set value

DECLARE condition_name CONDITION FOR condition_value
condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | mysql_error_code

DECLARE handler_type HANDLER FOR condition_value[,...] statement

handler_type:
    CONTINUE
  | EXIT (Default)
  | UNDO (Not yet supported)

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  | mysql_error_code

CREATE TABLE t (s1 int,primary key (s1));

create procedure handlerdemo()
begin
  declare continue handler for sqlstate '23000' set @x2=1;
  set @x=1;
  insert into t values(1);
  set @x=2;
  insert into t values(2);
  set @x=3;
end;

CALL handlerdemo();
select @x;


//Cursor Demo
CREATE TABLE t1 (id char(16),data int, primary key (id));
CREATE TABLE t2 (i int,primary key (i));
CREATE TABLE t3 (id char(16),data int, primary key (id));

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN cur1;
  OPEN cur2;

  REPEAT
    FETCH cur1 INTO a, b; 'fetch next row and advances the cursor pointer: 指针应该是指向两行之间'
    FETCH cur2 INTO c;
    IF NOT done THEN
       IF b < c THEN
          INSERT INTO test.t3 VALUES (a,b);
       ELSE
          INSERT INTO test.t3 VALUES (a,c);
       END IF;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
  CLOSE cur2;
END;

################
procedures:using Call statement. 无返回, 参数可以是IN, OUT, INOUT

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
proc_parameter:
    [ IN | OUT | INOUT ] param_name type : Default IN
    
如果变量名和column name一样,MySQL解析成变量

>create procedure wei(out param1 int)
>begin
 > select count(*) into param1 from test1;
>end;

>call wei(@a);
>select @a;

<===> 
>select count(*) into @a from test1;
>select @a;

############################################################################
function: can be invoked from inside a statement. 需定义return type, 所有参数都是传入
CREATE FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

create function hello(s char(20)) returns char(50)
return concat('Hello, ', s, '!');

>select hello('wei');

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]: 只能修改特征

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

###Flow control
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN ITERATE label1; 'do the loop label1 again'
    END IF;
    LEAVE label1;
  END LOOP label1;
  SET @x = p1;
END
create procedure dorepeat(p1 int)
 begin
  set @x=0;
  repeat
   set @x=@x+1;
   until @x>p1
  end repeat;
 end

### binary log 
1. For replication
2. data recovery

*/
public class UserProcedure{
	private Long id;
	private String name;
	private String address;
	private String notmapped;
	
	public UserProcedure(){
	}
	
	public UserProcedure(String s){
		name=s;
	}
	
	public Long getId(){
		return id;
	}
	public void setId(Long i){
		id=i;
	}
	
	public String getName(){
		return name;
	}
	public void setName(String i){
		name=i;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

    /**
     * @return the notmapped
     */
    public String getNotmapped() {
        return notmapped;
    }

    /**
     * @param notmapped the notmapped to set
     */
    public void setNotmapped(String notmapped) {
        this.notmapped = notmapped;
    }
}